const mysql = require('mysql2/promise');
const config = require('./config');

// 数据库配置
const dbConfig = {
  host: config.DB_HOST,
  user: config.DB_USER,
  password: config.DB_PASSWORD,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
};

// 创建初始连接池（不指定数据库）
const initialPool = mysql.createPool(dbConfig);

// 定义表结构
const TABLES = {
  users: `
  CREATE TABLE IF NOT EXISTS users(
    user_account VARCHAR(20) PRIMARY KEY,          
    user_name VARCHAR(50) NOT NULL,                
    user_password VARCHAR(100) NOT NULL,           
    register_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    recently TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
    INDEX idx_user_name (user_name),               
    INDEX idx_register_time (register_time)      
  ) COMMENT '用户信息表';
  `,
  examine_scores: `
  CREATE TABLE IF NOT EXISTS examine_scores (
    user_score_id INT AUTO_INCREMENT PRIMARY KEY,      
    user_account VARCHAR(20) NOT NULL,                 
    user_name VARCHAR(50) NOT NULL,                    
    question_count INT NOT NULL, 
    user_grade INT NOT NULL CHECK (user_grade >= 0),  
    exam_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
    examine_true_num INT NOT NULL CHECK (examine_true_num >= 0), 
    examine_false_num INT NOT NULL CHECK (examine_false_num >= 0), 
    CONSTRAINT fk_examine_scores_user_account
        FOREIGN KEY (user_account) 
        REFERENCES users(user_account) 
        ON DELETE CASCADE,
    CONSTRAINT chk_question_counts 
        CHECK (examine_true_num + examine_false_num = question_count),
    INDEX idx_user_account (user_account),
    INDEX idx_exam_date (exam_date),
    INDEX idx_user_grade (user_grade),
    INDEX idx_user_name (user_name)
  ) COMMENT '考核成绩表';
  `
};

// 存储过程定义
const STORED_PROCEDURES = {
  GetUserScoreSummaryCombined: `
    CREATE PROCEDURE GetUserScoreSummaryCombined(
        IN Today_start_date DATETIME,
        IN Today_end_date DATETIME,
        IN week_start_date DATETIME,
        IN week_end_date DATETIME,
        IN month_start_date DATETIME,
        IN month_end_date DATETIME
    )
    BEGIN
        SELECT 
            user_account,
            user_name,
            -- 今日时间段的统计
            SUM(CASE WHEN exam_date BETWEEN Today_start_date AND Today_end_date 
                    THEN question_count ELSE 0 END) AS Today_question_count,
            SUM(CASE WHEN exam_date BETWEEN Today_start_date AND Today_end_date  
                    THEN examine_true_num ELSE 0 END) AS Today_examine_true_num,
            -- 本周时间段的统计
            SUM(CASE WHEN exam_date BETWEEN week_start_date AND week_end_date
                    THEN question_count ELSE 0 END) AS week_question_count,
            SUM(CASE WHEN exam_date BETWEEN week_start_date AND week_end_date
                    THEN examine_true_num ELSE 0 END) AS week_examine_true_num,
            -- 本月时间段的统计
            SUM(CASE WHEN exam_date BETWEEN month_start_date AND month_end_date
                    THEN question_count ELSE 0 END) AS month_question_count,
            SUM(CASE WHEN exam_date BETWEEN month_start_date AND month_end_date
                    THEN examine_true_num ELSE 0 END) AS month_examine_true_num,
            -- 总体统计
            SUM(question_count) AS total_question_count,
            SUM(examine_true_num) AS total_examine_true_num
        FROM 
            examine_scores
        WHERE 
            exam_date IS NOT NULL   
        GROUP BY 
            user_account, user_name;
    END
      `
};

// 主连接池
let mainPool;

// 初始化数据库
async function initializeDatabase() {
  let connection;
  try {
    // 获取连接
    connection = await initialPool.getConnection();
    
    // 检查并创建数据库
    console.log('检查并创建数据库...');
    try {
      const [existingDbs] = await connection.query(`
        SELECT SCHEMA_NAME 
        FROM INFORMATION_SCHEMA.SCHEMATA 
        WHERE SCHEMA_NAME = ?
      `, [config.DB_NAME]);
    
      if (existingDbs.length === 0) {
        await connection.query(`CREATE DATABASE \`${config.DB_NAME}\``);
        console.log(`✅ 数据库 ${config.DB_NAME} 创建成功`);
      } else {
        console.log(`✅ 数据库 ${config.DB_NAME} 已存在`);
      }
    } catch (err) {
      console.error(`❌ 数据库操作失败: ${err.message}`);
      throw err;
    }

    // 使用新数据库
    await connection.query(`USE ${config.DB_NAME}`);
    
    // 创建表结构
    console.log('检查并创建表结构...');
    for (const [tableName, query] of Object.entries(TABLES)) {
      try {
        await connection.query(query);
        console.log(`✅ 表/视图 ${tableName} 已就绪`);
      } catch (err) {
        console.log(`⚠️ 表/视图 ${tableName} 已存在，跳过创建`);
      }
    }


    // 创建存储过程
    console.log('检查并创建存储过程...');
    for (const [procedureName, query] of Object.entries(STORED_PROCEDURES)) {
      try {
        // 直接使用 DROP PROCEDURE IF EXISTS 删除存储过程（如果存在）
        await connection.query(`DROP PROCEDURE IF EXISTS ${procedureName}`);
        console.log(`🔄 存储过程 ${procedureName} 已处理（存在则删除）`);
        
        // 创建存储过程
        await connection.query(query);
        console.log(`✅ 存储过程 ${procedureName} 创建成功`);
      } catch (err) {
        console.error(`❌ 存储过程 ${procedureName} 创建失败:`, err.message);
        // 存储过程创建失败不中断整个流程，继续执行
      }
    }




    console.log('✅ 数据库初始化完成');
  } catch (err) {
    console.error('❌ 数据库初始化失败:', err);
    throw err;
  } finally {
    if (connection) connection.release();
  }
}

// 初始化主连接池
async function createMainPool() {
  try {
    mainPool = mysql.createPool({
      ...dbConfig,
      database: config.DB_NAME
    });
    
    // 测试连接
    const testConn = await mainPool.getConnection();
    console.log('数据库连接信息:');
    console.log('数据库:', config.DB_NAME);
    console.log('地址:', config.DB_HOST);
    console.log('用户:', config.DB_USER);
    testConn.release();
    
    console.log('✅ 数据库连接测试成功');
    console.log('✅ ------------------------服务器端口监听中-----------------------');
    console.log('......');
    return mainPool;
  } catch (err) {
    console.error('❌ 主连接池创建失败:', err);
    throw err;
  }
}

// 导出Promise包装的连接池
module.exports = initializeDatabase()
  .then(() => createMainPool())
  .then(pool => {
    // 导出前测试连接
    return pool.getConnection()
      .then(conn => {
        conn.release();
        return pool;
      });
  })
  .catch(err => {
    console.error('❌ 数据库系统初始化失败:', err);
    process.exit(1);
  });